<?php

namespace app\admin\controller;

use app\model\Database as ModelDatabase;
use think\Db;


class DataBackup extends Member
{
    public function _initialize()
    {
        parent::_initialize();
    }

    /**
     * 备份数据列表 \n
     * URI : /admin/database.list
     * @param :
     *  name       | type   | null | description
     * ------------|--------|------|-------------
     *  passport   | string | 必填    | 凭证
     *
     *  page       | int    | 必填  | 页码
     *  listRows   | int    | 必填  | 一页的条数
     *
     * @return :
     *  name       | type   | description
     * ------------|--------|-------------
     *  totalPages | string | 总页数
     *  list       | array  | 列表
     *
     * list:
     *  name       | type   | description
     * ------------|--------|-------------
     *  databaseId | int    | 数据库备份表
     *  name       | string | 名称
     *  version    | string | 版本号
     *  size       | string | 文件大小
     *  gmtCreate  | string | 添加时间
     *
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function databaseList(){
        $int_now_page = $this->_getContentParams('page'); // 当前页码
        _toValidate('id', $int_now_page, 10005);
        $int_list_rows = $this->_getContentParams('listRows'); // 一页的条数
        _toValidate('id', $int_list_rows, 10006);

        $arr_where = [];
        $int_total_rows = ModelDatabase::getInstance()->toCount($arr_where);
        $int_total_pages = (int)ceil($int_total_rows / $int_list_rows);//总页数
        _toCompare(($int_now_page > $int_total_pages && $int_total_pages > 0), '===', true, 10007);
        $int_first_row = $int_list_rows * ($int_now_page - 1);//从第几条开始查询
        $str_field = 'id AS databaseId,name,version,size,gmt_create AS gmtCreate';
        $arr_list = ModelDatabase::getInstance()->toSelect($arr_where, $str_field, $int_first_row, $int_list_rows, 'gmtCreate');
        _returnData(['totalPages' => $int_total_pages, 'list' => $arr_list]);
    }

    /**
     * 备份数据 \n
     * URI : /admin/export.database
     * @param :
     *  name       | type   | null | description
     * ------------|--------|------|-------------
     *  passport   | string | 必填  | 凭证
     *
     * @return :
     * name        | type   | description
     * ------------|--------|-------------
     *  ---------- | ------ | 无
     *
     */
    public function exportDatabase(){
        header("Content-type:text/html;charset=utf-8");
        $path = RUNTIME_PATH.'mysql/';

        //版本号
        $database_version = '1.0';
        $database = config('database')['database'];
//echo "运行中，请耐心等待...<br/>";
        $info = "";
//        $info .= "-- 日期：".date("Y-m-d H:i:s",time())."\r\n";
//        $info .= "-- MySQL - 5.5.52-MariaDB : Database - ".$database."\r\n";
//        $info .= "-- ----------------------------\r\n\r\n";
//        $info .= "CREATE DATAbase IF NOT EXISTS `".$database."` DEFAULT CHARACTER SET utf8 ;\r\n\r\n";
//        $info .= "USE `".$database."`;\r\n\r\n";

// 检查目录是否存在
        if(is_dir($path)){
// 检查目录是否可写
            if(is_writable($path)){
//echo '目录可写';exit;
            }else{
//echo '目录不可写';exit;
                chmod($path,0777);
            }
        }else{
//echo '目录不存在';exit;
// 新建目录
            mkdir($path, 0777, true);
//chmod($path,0777);
        }

// 检查文件是否存在
        $file_name = $path.$database.'-'.date("Y-m-d",time()).'.sql';
        _toCompare(file_exists($file_name), '===', true, 30212);
        file_put_contents($file_name,$info,FILE_APPEND);
//查询数据库的所有表
        $result = Db::query('show tables');
//print_r($result);exit;
        foreach ($result as $k => $v) {
//查询表结构
        if($v['Tables_in_tmwl_dev'] == "database"){
            continue; // 当 $value为b时，跳出本次循环
        }
            $val = $v['Tables_in_'.$database];
            $sql_table = "show create table ".$val;
            $res = Db::query($sql_table);
//print_r($res);exit;
            $info_table = "-- ----------------------------\r\n";
            $info_table .= "-- Table structure for `".$val."`\r\n";
            $info_table .= "-- ----------------------------\r\n\r\n";
            $info_table .= "DROP TABLE IF EXISTS `".$val."`;\r\n\r\n";
            $info_table .= $res[0]['Create Table'].";\r\n\r\n";
//查询表数据
            $info_table .= "-- ----------------------------\r\n";
            $info_table .= "-- Data for the table `".$val."`\r\n";
            $info_table .= "-- ----------------------------\r\n\r\n";
            file_put_contents($file_name,$info_table,FILE_APPEND);
            $sql_data = "select * from ".$val;
            $data = Db::query($sql_data);
//print_r($data);exit;
            $count= count($data);
//print_r($count);exit;
            if($count<1) continue;
            foreach ($data as $key => $value){
                $sqlStr = "INSERT INTO `".$val."` VALUES (";
                foreach($value as $v_d){
                    $v_d = str_replace("'","\'",$v_d);
                    $sqlStr .= "'".$v_d."', ";
                }
//需要特别注意对数据的单引号进行转义处理
//去掉最后一个逗号和空格
                $sqlStr = substr($sqlStr,0,strlen($sqlStr)-2);
                $sqlStr .= ");\r\n";
                file_put_contents($file_name,$sqlStr,FILE_APPEND);
            }

            $info = "\r\n";
            file_put_contents($file_name,$info,FILE_APPEND);
        }
        $path = RUNTIME_PATH.'mysql/';

        $size = filesize($path.$database.'-'.date("Y-m-d",time()).'.sql');
        $arr_data = [
            'name' => $database.'-'.date("Y-m-d",time()).'.sql',
            'version' => $database_version,
            'admin_id' => $this->admin_id,
            'size' => $size,
        ];
        ModelDatabase::getInstance()->toAdd($arr_data);
        $this->operationLogAdd($this->_now.'备份数据');
        _returnData();

    }

    /**
     * 备份数据还原 \n
     * URI : /admin/restore.database
     * @param :
     *  name       | type   | null | description
     * ------------|--------|------|-------------
     *  passport   | string | 必填  | 凭证
     *  dataId     | int    | 必填  | id编号
     *
     * @return :
     * name        | type   | description
     * ------------|--------|-------------
     *  ---------- | ------ | 无
     *
     */
    public function restoreDatabase(){
        ini_set("max_execution_time", "0");
        $database_id = $this->_getContentParams('dataId');
        _toValidate('id', $database_id, 10009);
        $str_name = ModelDatabase::getInstance()->toFind(['id' => $database_id, 'admin_id' => $this->admin_id], 'name');

        $path = RUNTIME_PATH.'mysql/'.$str_name['name'];

        //读取文件内容
        $_sql = file_get_contents($path);
        $_arr = explode(';', $_sql);

        //执行sql语句
        $count = count($_arr)-1;

        foreach ($_arr as $key => $_value) {
            if($key == $count){
             break;
            }
            Db::execute($_value.';');
        }
        $this->operationLogAdd($this->_now.'备份数据还原');
        _returnData();
    }


    /**
     * 删除数据备份 \n
     * URI : /admin/delete.database
     * @param :
     *  name       | type   | null | description
     * ------------|--------|------|-------------
     *  passport   | string | 必填  | 凭证
     *  dataId     | int    | 必填  | id编号
     *
     * @return :
     * name        | type   | description
     * ------------|--------|-------------
     *  ---------- | ------ | 无
     *
     * @throws \think\Exception
     * @throws \think\exception\PDOException
     */
    public function deleteDatabase(){
        $database_id = $this->_getContentParams('dataId');
        _toValidate('id', $database_id, 10009);
        $str_name = ModelDatabase::getInstance()->toFind(['id' => $database_id, 'admin_id' => $this->admin_id], 'name');
        $path = RUNTIME_PATH.'mysql/'.$str_name['name'];
//        $this->toCompare(strpos($path,'.sql'), '===', false, 19184);
        ModelDatabase::getInstance()->toDelete(['id' => $database_id, 'admin_id' => $this->admin_id]);
        $this->operationLogAdd($this->_now.'删除数据备份');
        unlink($path);
        _returnData();
    }

}
